package com.wydstation.power.properties.excel;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel导入工具
 *
 * @author xuxueli 2017-09-08 22:41:19
 */
public class ExcelImportUtil<T> {
    private static Logger logger = LoggerFactory.getLogger(ExcelImportUtil.class);
    private   final Map<String, List<T>> map = new HashMap<>();

    /**
     * 从Workbook导入Excel文件，并封装成对象
     *
     * @param workbook
     * @param sheetClass
     * @return
     */
    public static <T> List<T> importExcel(Workbook workbook, Class<T> sheetClass, int... index) {
        List<T> sheetDataList = importSheet(workbook, sheetClass, index);
        return sheetDataList;
    }

    public  /* <T> List<T>*/Map<String, List<T>> mutilImportExcel(InputStream inputStream, Class<T> sheetClass) {

        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                map.put( workbook.getSheetName(i),importSheet(workbook, sheetClass, i));
            }
            return map;
        } catch (Exception e) {
            throw new RuntimeException("解析exlce失败");
        }
    }



    /**
     * @param workbook
     * @param sheetClass
     * @param index      index[0] sheet页索引, index[1] 开始行号
     * @param <T>
     * @return
     */
    public static <T> List<T> importSheet(Workbook workbook, Class<T> sheetClass, int... index) {
        try {

            // sheet data
            Sheet sheet = workbook.getSheetAt(index[0]);
            if (sheet == null) {
                return null;
            }
            return excleToDto(sheetClass, sheet, index);

        } catch (IllegalAccessException e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (InstantiationException e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 导入Excel文件，并封装成workbook
     *
     * @param excelFile
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     * @throws EncryptedDocumentException
     */
    public static Workbook importExcel(File excelFile)
            throws EncryptedDocumentException, InvalidFormatException, IOException {
        return WorkbookFactory.create(excelFile);
    }

    private static <T> List<T> excleToDto(Class<T> sheetClass, Sheet sheet, int[] index)
            throws InstantiationException, IllegalAccessException {

        // sheet field
        List<Field> fields = new ArrayList<Field>();
        List<Field> parentField = FieldReflectionUtil.getParentField(sheetClass);
        if (parentField != null && parentField.size() > 0) {
            for (Field field : parentField) {
                if (Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                fields.add(field);
            }
        }

        if (fields == null || fields.size() == 0) {
            throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
        }

        Iterator<Row> sheetIterator = sheet.rowIterator();
        int rowIndex = 0;
        int startIndex = 0;
        if (index != null && index.length > 1) {
            startIndex = index[1];
        }
        List<T> dataList = new ArrayList<>();
        while (sheetIterator.hasNext()) {
            Row rowX = sheetIterator.next();
            if (rowIndex > startIndex) {
                T rowObj = sheetClass.newInstance();
                for (int i = 0; i < fields.size(); i++) {

                    // cell
                    Cell cell = rowX.getCell(i);
                    if (cell == null) {
                        continue;
                    }

                    // call val str
                    String fieldValueStr = null;
                    if (cell.getCellTypeEnum() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
                        short format = cell.getCellStyle().getDataFormat();
                        SimpleDateFormat sdf = null;
                        if (format == 14 || format == 31 || format == 57 || format == 58) {
                            // 日期
                            sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        } else if (format == 20 || format == 32) {
                            // 时间
                            sdf = new SimpleDateFormat("HH:mm");
                        } else {
                            sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        }
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        fieldValueStr = sdf.format(date);
                    } else {

                        cell.setCellType(CellType.STRING);
                        fieldValueStr = cell.getStringCellValue(); // cell.getCellTypeEnum()
                    }
                    // java val
                    Field field = fields.get(i);
                    Object fieldValue = FieldReflectionUtil.parseValue(field, fieldValueStr);
                    if (fieldValue == null) {
                        continue;
                    }

                    // fill val
                    field.setAccessible(true);
                    field.set(rowObj, fieldValue);
                }
                dataList.add(rowObj);
            }
            rowIndex++;
        }
        return dataList;
    }

    /**
     * 导入Excel文件，并封装成对象
     *
     * @param excelFile
     * @param sheetClass
     * @return
     */
    public static <T> List<T> importExcel(File excelFile, Class<T> sheetClass, int... index) {
        try {
            Workbook workbook = WorkbookFactory.create(excelFile);
            List<T> dataList = importExcel(workbook, sheetClass, index);
            return dataList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 从文件路径导入Excel文件，并封装成对象
     *
     * @param filePath
     * @param sheetClass
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Class<T> sheetClass, int... index) {
        File excelFile = new File(filePath);
        List<T> dataList = importExcel(excelFile, sheetClass, index);
        return dataList;
    }

    /**
     * 导入Excel数据流，并封装成对象
     *
     * @param inputStream
     * @param sheetClass
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Class<T> sheetClass, int... index) {
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            List<T> dataList = importExcel(workbook, sheetClass, index);
            return dataList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }
    public static <T> List<T> mutilImportExcel(Workbook workbook, Class<T> sheetClass) {

        int numberOfSheets = workbook.getNumberOfSheets();
        List<T> sheetDataList = new ArrayList<>();
        for (int i = 0; i < numberOfSheets; i++) {
            List<T> ts = importSheet(workbook, sheetClass, i);
            if (ts != null) {
                sheetDataList.addAll(ts);
            }
        }
        return sheetDataList;
    }

    public static <T> List<T> importExcel(Workbook workbook, Class<T> sheetClass) {

        List<T> dtoList = new ArrayList<>();

        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            List<T> temp = importSheet(workbook, sheetClass, i);
            if (temp != null && temp.size() > 0) {
                dtoList.addAll(temp);
            }
        }

        return dtoList;
    }

    /**
     * @param workbook
     * @param sheetClass
     * @param index      index[0] sheet页索引, index[1] 开始行号
     * @param <T>
     * @return
     */
    public static <T> List<T> importSheet(Workbook workbook, Class<T> sheetClass, String sheetName, int... index) {
        try {

            // sheet data
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                return null;
            }
            return excleToDto(sheetClass, sheet, new int[]{0, index[0]});

        } catch (IllegalAccessException e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (InstantiationException e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }
}
